package com.maduixa.guitar.tabsearch.spring.data;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.maduixa.guitar.tabsearch.spring.business.model.GuitarUser;
import com.maduixa.guitar.tabsearch.spring.business.model.Song;
import com.maduixa.guitar.tabsearch.spring.business.model.UserSong;


public class DataController {
	
	
	/*
	 * Users
	 */
    public void addUser(GuitarUser u){
    	Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	
    	try {
			tr = hs.beginTransaction();
			hs.save(u);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
    	
    }
    
    public void updateUser(GuitarUser u){
    	Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	
    	try {
			tr = hs.beginTransaction();
			hs.update(u);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
    	
    }
    
    public GuitarUser getUser(String uName) {
    	GuitarUser gu = new GuitarUser();
    	try {
	    	Session hs = HibernateUtil.getSessionFactory().openSession();
	    	Transaction tr = null;
	    	try {
				tr = hs.beginTransaction();
				hs.load(gu, uName);
				tr.commit();
			} catch (HibernateException e) {
				tr.rollback();
				e.printStackTrace();
				gu = null;
			} finally {
				hs.close();
			}
    	} catch (Exception e) {
    		HibernateUtil.configureSessionFactory();
    		//Apanyo
    		Session hs = HibernateUtil.getSessionFactory().openSession();
	    	Transaction tr = null;
	    	try {
				tr = hs.beginTransaction();
				hs.load(gu, uName);
				tr.commit();
			} catch (HibernateException er) {
				tr.rollback();
				er.printStackTrace();
				gu = null;
			} finally {
				hs.close();
			}
	    	//fi apanyo
		}
    	return gu;
    }
    
    @SuppressWarnings("unchecked")
	public List<Object> getAllUsersByLevel() {
    	Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	List<Object> users = null;
    	try {
			tr = hs.beginTransaction();
			users = hs.createQuery(
					"select gu.username, gu.mail, gu.admin, gu.dataAlta, count(gu.username), us.level "+
					"from UserSong us, GuitarUser gu "+
					"where us.userId = gu.username " +
					"group by gu.username, gu.mail, gu.admin, gu.dataAlta, us.level"
					).list();
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
			users = null;
		} finally {
			hs.close();
		}
    	return users;
    }
    
    
    /*
     * Songs
     */

	public void addSong(Song s){
    	Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	
    	try {
			tr = hs.beginTransaction();
			hs.save(s);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
    	
    }
	
	public void updateSong(Song s){
    	Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	
    	try {
			tr = hs.beginTransaction();
			hs.update(s);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
    	
    }
	
	public void removeSong(Song s) {
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	
    	try {
			tr = hs.beginTransaction();
			hs.delete(s);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
	}
	
	public Song getSong(long id) {
    	Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	Song so = new Song();
    	try {
			tr = hs.beginTransaction();
			hs.load(so, id);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
			so = null;
		} finally {
			hs.close();
		}
    	return so;
    }

	@SuppressWarnings("unchecked")
	public List<Object> getAllSongs(String userId) {
    	Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	List<Object> songs = null;
    	try {
			tr = hs.beginTransaction();
			
			Query query = hs.createSQLQuery(
					"select s.id, s.artist, s.song, s.tune, s.guitar1, s.guitar2, s.guitar3, " +
							" s.bass, s.solo, us_id " +
					"from  ( " +
								"select song_id as s_id, user_id as us_id " +
								"from USER_SONG " +
								"where user_id = :username" +
							") us " +
					"right join SONG s " +
					"on s.id = s_id");
			
			query.setParameter("username", userId);
			
			songs = query.list();
			
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
			songs=null;
		} finally {
			hs.close();
		}
    	
    	return songs;
    }
	
	@SuppressWarnings("unchecked")
	public List<Object> getSongs(String userId, String artist, String song, String tune, String order, String direction) {
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	List<Object> songs = null;
    	try {
			tr = hs.beginTransaction();
			
			String sQuery = "select s.id, s.artist, s.song, s.tune, s.guitar1, s.guitar2, s.guitar3, " +
									" s.bass, s.solo, us_id " +
							"from  ( " +
										"select song_id as s_id, user_id as us_id " +
										"from USER_SONG " +
										"where user_id = :username" +
									") us " +
							"right join SONG s " +
							"on s.id = s_id ";
			if (artist !=null) sQuery += "where s.artist like '%"+artist+"%'";
			if (song !=null){
				if (sQuery.contains("s_id where")) 
					sQuery+= " and ";
				else
					sQuery += "where ";
				sQuery += "s.song like '%"+song+"%'";
			}
			
			if (tune !=null){
				if (sQuery.contains("s_id where")) 
					sQuery+= " and ";
				else
					sQuery += "where ";
				sQuery += "s.tune = '"+tune+"'";
			}
			
			if(order != null){
				sQuery+=" order by s."+order+" "+direction;
			}
			else
				sQuery+=" order by s.artist, s.song";
			
			Query query = hs.createSQLQuery(sQuery);
			
			query.setParameter("username", userId);
			
			songs = query.list();
			
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
			songs=null;
		} finally {
			hs.close();
		}
    	
    	return songs;
	}

	
	/*
	 * UserSongs
	 */
	
	public void addUserSong(UserSong us) {
		
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	
    	try {
			tr = hs.beginTransaction();
			hs.save(us);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
		
	}
	
	public void updateUserSong(UserSong us) {
		
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	
    	try {
			tr = hs.beginTransaction();
			hs.update(us);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
		
	}
	
	public void removeMySong(long id) {
		
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	UserSong usrSo = new UserSong();
    	try {
			tr = hs.beginTransaction();
			hs.load(usrSo, id);
			hs.delete(usrSo);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
		} finally {
			hs.close();
		}
		
	}
	
	public UserSong getUserSong(long id) {
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	UserSong so = new UserSong();
    	try {
			tr = hs.beginTransaction();
			hs.load(so, id);
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
			so = null;
		} finally {
			hs.close();
		}
    	return so;
	}
	
	@SuppressWarnings("unchecked")
	public List<Object> getMySongs(String uName, String order, String direction) {
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	List<Object> songs = null;
    	try {
			tr = hs.beginTransaction();
			
			String queryStr = "select us.id, us.song.artist, us.song.song, us.song.tune, us.record, us.cover, us.level " +
					"from UserSong us " +
					"where userId = :id ";
			if(order != null){
				queryStr+="order by us.level, us.song."+order+" "+direction;
			}
			else
				queryStr+="order by us.level, us.song.artist";
			
			Query query = hs.createQuery(queryStr);
			query.setParameter("id", uName);
			
			songs = query.list();
			
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
			songs=null;
		} finally {
			hs.close();
		}
    	return songs;
	}

	@SuppressWarnings("unchecked")
	public List<UserSong> getAllUserSong(Song s) {
		Session hs = HibernateUtil.getSessionFactory().openSession();
    	Transaction tr = null;
    	List<UserSong> songs = null;
    	try {
			tr = hs.beginTransaction();
			
			Query query = hs.createQuery("from UserSong where song = :s ");
			query.setParameter("s", s);
			
			songs = query.list();
			
			tr.commit();
		} catch (HibernateException e) {
			tr.rollback();
			e.printStackTrace();
			songs=null;
		} finally {
			hs.close();
		}
    	return songs;
	}
    
}
